﻿Imports System.Data.SqlClient
Imports UTNComputers.clsEmpleado
Imports UTNComputers.daoRoles
Imports UTNComputers.clsRoles

Public Class daoEmpleado
    'Funcion para Traer un Empleado por Id'
    Public Function TraeEmpleado(ByVal emp_id As Integer) As clsEmpleado
        Dim Cn As New SqlClient.SqlConnection
        Dim Cm As New SqlClient.SqlCommand
        Dim Dr As SqlClient.SqlDataReader
        Dim Empleado As New clsEmpleado()

        Dim Conf As New clsConfiguraciones()
        Dim dao_Conf As New daoConfiguraciones()
        Conf = dao_Conf.TraeConfiguraciones()

        Try
            Cn.ConnectionString = My.Resources.Conexion
            Cn.Open()
            Cm.CommandType = CommandType.StoredProcedure
            Cm.CommandText = "SP_TRAER_EMPLEADO"

            Cm.Parameters.Add("@emp_id", SqlDbType.Int)
            Cm.Parameters("@emp_id").Value = emp_id

            Cm.Parameters.Add("@Cod_Suc", SqlDbType.Int)
            Cm.Parameters("@Cod_Suc").Value = Conf.Cod_Suc

            Cm.Connection = Cn
            Dr = Cm.ExecuteReader
            If Dr.HasRows = True Then
                While Dr.Read
                    Empleado.Emp_Id = Dr.GetInt32(0)
                    Empleado.Nombre = Dr.GetString(1).Trim.ToUpper
                    Empleado.Apellido = Dr.GetString(2).Trim.ToUpper
                    Empleado.Rol_Id = Dr.GetInt32(3)
                    Empleado.Cod_Suc = Dr.GetInt32(4)

                    Dim RolDao As New daoRoles()
                    Empleado.Rol = RolDao.TraeRol(Empleado.Rol_Id)
                    RolDao.Dispose()

                End While

                Dr.Close()
                Cn.Close()
            End If
            Return Empleado
        Catch ex As Exception
            Throw New Exception(ex.Message)
        Finally
            Cn.Dispose()
            Cm.Dispose()
        End Try
    End Function

    'Funcion para Traer un Empleado por rol'
    Public Function TraeEmpleado_PorRol(ByVal rol_id As Integer) As List(Of clsEmpleado)
        Dim Cn As New SqlClient.SqlConnection
        Dim Cm As New SqlClient.SqlCommand
        Dim Dr As SqlClient.SqlDataReader
        Dim ListaEmpleados As New List(Of clsEmpleado)

        Dim Conf As New clsConfiguraciones()
        Dim dao_Conf As New daoConfiguraciones()
        Conf = dao_Conf.TraeConfiguraciones()

        Try
            Cn.ConnectionString = My.Resources.Conexion
            Cn.Open()
            Cm.CommandType = CommandType.StoredProcedure
            Cm.CommandText = "SP_TRAER_EMPLEADO_X_ROL"

            Cm.Parameters.Add("@rol_id", SqlDbType.Int)
            Cm.Parameters("@rol_id").Value = rol_id
            Cm.Parameters.Add("@Cod_Suc", SqlDbType.Int)
            Cm.Parameters("@Cod_Suc").Value = Conf.Cod_Suc

            Cm.Connection = Cn
            Dr = Cm.ExecuteReader
            If Dr.HasRows = True Then
                While Dr.Read
                    Dim Empleado As New clsEmpleado()
                    Empleado.Emp_Id = Dr.GetInt32(0)
                    Empleado.Nombre = Dr.GetString(1).Trim.ToUpper
                    Empleado.Apellido = Dr.GetString(2).Trim.ToUpper
                    Empleado.Rol_Id = Dr.GetInt32(3)
                    Empleado.Cod_Suc = Dr.GetInt32(4)

                    Dim RolDao As New daoRoles()
                    Empleado.Rol = RolDao.TraeRol(Empleado.Rol_Id)
                    RolDao.Dispose()

                    ListaEmpleados.Add(Empleado)
                    Empleado.Dispose()

                End While
                Dr.Close()
                Cn.Close()
            End If
            Return ListaEmpleados
        Catch ex As Exception
            Throw New Exception(ex.Message)
        Finally
            Cn.Dispose()
            Cm.Dispose()
        End Try
    End Function

    'Funcion para Traer Todos los Empleados'
    Public Function TraeEmpleados() As List(Of clsEmpleado)
        Dim Cn As New SqlClient.SqlConnection
        Dim Cm As New SqlClient.SqlCommand
        Dim Dr As SqlClient.SqlDataReader
        Dim ListaEmpleados As New List(Of clsEmpleado)

        Dim Conf As New clsConfiguraciones()
        Dim dao_Conf As New daoConfiguraciones()
        Conf = dao_Conf.TraeConfiguraciones()

        Try
            Cn.ConnectionString = My.Resources.Conexion
            Cn.Open()
            Cm.CommandType = CommandType.StoredProcedure
            Cm.CommandText = "SP_TRAER_EMPLEADOS_TODOS"

            Cm.Parameters.Add("@Cod_Suc", SqlDbType.Int)
            Cm.Parameters("@Cod_Suc").Value = Conf.Cod_Suc

            Cm.Connection = Cn
            Dr = Cm.ExecuteReader
            If Dr.HasRows = True Then
                While Dr.Read
                    Dim Empleado As New clsEmpleado()
                    Empleado.Emp_Id = Dr.GetInt32(0)
                    Empleado.Nombre = Dr.GetString(1).Trim.ToUpper
                    Empleado.Apellido = Dr.GetString(2).Trim.ToUpper
                    Empleado.Rol_Id = Dr.GetInt32(3)
                    Empleado.Cod_Suc = Dr.GetInt32(4)

                    Dim RolDao As New daoRoles()
                    Empleado.Rol = RolDao.TraeRol(Empleado.Rol_Id)
                    RolDao.Dispose()

                    ListaEmpleados.Add(Empleado)
                    Empleado.Dispose()

                End While
                Dr.Close()
                Cn.Close()
            End If
            Return ListaEmpleados
        Catch ex As Exception
            Throw New Exception(ex.Message)
        Finally
            Cn.Dispose()
            Cm.Dispose()
        End Try
    End Function

    'Funcion para Traer un Empleado por Id'
    Public Function TraeEmpleadoCodSuc(ByVal emp_id As Integer, ByVal Cod_Suc As Int32) As clsEmpleado
        Dim Cn As New SqlClient.SqlConnection
        Dim Cm As New SqlClient.SqlCommand
        Dim Dr As SqlClient.SqlDataReader
        Dim Empleado As New clsEmpleado()

        Try
            Cn.ConnectionString = My.Resources.Conexion
            Cn.Open()
            Cm.CommandType = CommandType.StoredProcedure
            Cm.CommandText = "SP_TRAER_EMPLEADO"

            Cm.Parameters.Add("@emp_id", SqlDbType.Int)
            Cm.Parameters("@emp_id").Value = emp_id

            Cm.Parameters.Add("@Cod_Suc", SqlDbType.Int)
            Cm.Parameters("@Cod_Suc").Value = Cod_Suc

            Cm.Connection = Cn
            Dr = Cm.ExecuteReader
            If Dr.HasRows = True Then
                While Dr.Read
                    Empleado.Emp_Id = Dr.GetInt32(0)
                    Empleado.Nombre = Dr.GetString(1).Trim.ToUpper
                    Empleado.Apellido = Dr.GetString(2).Trim.ToUpper
                    Empleado.Rol_Id = Dr.GetInt32(3)
                    Empleado.Cod_Suc = Dr.GetInt32(4)

                    Dim RolDao As New daoRoles()
                    Empleado.Rol = RolDao.TraeRol(Empleado.Rol_Id)
                    RolDao.Dispose()

                End While

                Dr.Close()
                Cn.Close()
            End If
            Return Empleado
        Catch ex As Exception
            Throw New Exception(ex.Message)
        Finally
            Cn.Dispose()
            Cm.Dispose()
        End Try
    End Function


    '---------------------------------------------------------------------
    ' Constructor de la Clase
    '---------------------------------------------------------------------
    Public Sub New()

        MyBase.New()

    End Sub

    '---------------------------------------------------------------------
    ' Destructor de la Clase
    '---------------------------------------------------------------------
    Public Sub Dispose()

        MyBase.Finalize()

    End Sub

End Class
